﻿using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using System.Xml.XPath;
using System.Xml;
//using System.Xml.Linq;
using System.Data;
using Npgsql;
using SpectationClient.DataBaseDescription;
using SpectationClient.SQLCommandBuilder;

namespace SpectationClient.DataBaseDescription {

    /// <summary>
    /// This class is used to read definitions of data base objects (tables, columns, keys, etc.)
    /// and to create a hierarcical DataBaseInfo Object.
    /// </summary>
    class SPECTATION_INFO_Reader {

    
        public struct DataBaseConnectionInfo {
            public String Value { get; set; }
            public String Description { get; set; }
        }



        public List<DataBaseConnectionInfo> ReadDBLocation() {
            XPathNavigator NAV = this.xmlDoc.CreateNavigator();
            List<DataBaseConnectionInfo> DBLs = new List<DataBaseConnectionInfo>();
            foreach(XPathNavigator N in NAV.Select("root/speclib_instances/instance")) {
                DataBaseConnectionInfo DBL = new DataBaseConnectionInfo();
                DBL.Value = this.readXML_getXPathNodeValue(N, "uri");
                String provider = this.readXML_getXPathNodeValue(N, "provider");
                String description = this.readXML_getXPathNodeValue(N, "description");
                DBL.Description = String.Format("{0} {1}", provider, description);
                DBLs.Add(DBL);
            }
            return DBLs;
        }

        public List<DataBaseConnectionInfo> ReadDBUsers() {
            XPathNavigator NAV = this.xmlDoc.CreateNavigator();
            List<DataBaseConnectionInfo> DBUs = new List<DataBaseConnectionInfo>();
            foreach(XPathNavigator N in NAV.Select("root/users/user")) {
                DataBaseConnectionInfo DBU = new DataBaseConnectionInfo();
                DBU.Value = this.readXML_getXPathNodeValue(N, "name");
                DBU.Description = this.readXML_getXPathNodeValue(N, "description");
                DBUs.Add(DBU);
            }
            return DBUs;
        }


        private Dictionary<String, ColumnInfoFile.RelatedColumnTypes> xml2ColumnType = new Dictionary<string, ColumnInfoFile.RelatedColumnTypes>();
        NpgsqlConnection con;
        //private XPathDocument xmlDoc;
        private XmlDocument xmlDoc = new XmlDocument();
        public SPECTATION_INFO_Reader(NpgsqlConnection connection, String xmlFilePath) { 
            this.con = connection;
            this.xmlDoc.Load(xmlFilePath);
           

        }

       

        private TableInfo getTableInfo(String schemaName, String tableName) {
            String schemaTableName = String.Format("\"{0}\".\"{1}\"", schemaName, tableName);
            TableInfo ti = new TableInfo(tableName);
            DataTable emptyTable = this.getEmptyTable(schemaTableName);

            //

            return ti;
        }

        private DataTable getNamespaces(String[] schemata, out List<Int64> schemaOIDs) {
            Condition cSchema = new Condition("nspname", Condition.Op.EQ);
            foreach(String schema in schemata) cSchema.Add(schema);
            DataTable dtNamespaces = this.getTable(new String[] { "oid", "nspname" }, "pg_namespace", cSchema);
            schemaOIDs = (from DataRow row in dtNamespaces.Rows
                                      select row["oid"]).Cast<Int64>().ToList<Int64>();

            return dtNamespaces;
        }

        private DataTable getClasses(List<Int64> schemaOIDs, out List<Int64> tableOIDs) {
            //clTableOIDs.Add(new Condition("relpersistence", Condition.Op.EQ, 'p'));
            //relname = tableName name
            //relnamespace = Schema name
            //relkind = result = ordinary tableName, i = iPosition, S = sequence, v = view, c = composite type, relatedColumnType = TOAST tableName, f = foreign tableName
            //relhaspkey	bool	 	True if the tableName has (or once had) a primary key
            //relchecks	int2	 	Number of CHECK constraints on the tableName; see pg_constraint catalog
           
            ConditionList clTableOIDs = new ConditionList(ConditionList.Op.AND);
            clTableOIDs.Add(new Condition("relnamespace", Condition.Op.EQ, schemaOIDs));
            clTableOIDs.Add(new Condition("relkind", Condition.Op.EQ, new String[]{"r","v"}));
            DataTable dt_pg_class = this.getTable(new String[] { "oid", "*" }, "pg_class", clTableOIDs);
            tableOIDs = (from DataRow row in dt_pg_class.Rows
                         select row["oid"]).Cast<Int64>().ToList<Int64>();
            return dt_pg_class;
        }

        private DataTable getConstraints(List<Int64> schemaOIDs) {
            //Get constraints (PKs, SKs, etc.)
            ConditionList clConstraints = new ConditionList(ConditionList.Op.AND);
            clConstraints.Add(new Condition("connamespace", Condition.Op.EQ, schemaOIDs));

            // c = check constraint, f = foreign key constraint, p = primary key constraint, 
            // u = unique constraint, relatedColumnType = constraint trigger, x = exclusion constraint
            clConstraints.Add(new Condition("contype", Condition.Op.EQ, new String[]{"p","f","c","u"}));
           
            //NpgsqlCommand cmd = clConstraints.getCmd();
            DataTable dt_pg_constraints = this.getTable("pg_constraint", clConstraints);
            return dt_pg_constraints;
        }

        private DataTable getPostGISColumns(String[] schemaNames) {
            ConditionList clConstraints = new ConditionList(ConditionList.Op.AND);
            clConstraints.Add(new Condition("f_table_schema", Condition.Op.EQ, schemaNames));
            DataTable dtPostGISColumns = this.getTable(new String[] { "*" }, "geometry_columns", clConstraints);
            
            return dtPostGISColumns;
        }

        private DataTable getAttributes(List<Int64> classOIDs) {
            ConditionList clAttribute = new ConditionList(ConditionList.Op.AND);
            clAttribute.Add(new Condition("attrelid", Condition.Op.EQ, classOIDs));
            clAttribute.Add(new Condition("attnum", Condition.Op.GT, 0));
            DataTable dtAttribute = this.getTable(new String[] { "*" }, "pg_attribute", clAttribute);
            return dtAttribute;
        }


        private Dictionary<Tuple<SchemaInfo, TableInfo>, DataTable> getEmptyTables3(DataTable dtClasses, ref DataBaseInfo DBI) {
            //Create list with all Schema-tableName names

            Dictionary<Tuple<SchemaInfo, TableInfo>, DataTable> dataTables = new Dictionary<Tuple<SchemaInfo, TableInfo>, DataTable>();

            //all all table and views to the data base information
            NpgsqlDataAdapter da = new NpgsqlDataAdapter();
           // this.con.Open();
            foreach(DataRow tableRow in dtClasses.Rows) {
                String schemaName = (String)tableRow["SCHEMA"];
                String tableName  = (String)tableRow["TABLE"];
                String relkind = (String)tableRow["relkind"];

                //Try to get empty table. if not possible: continue
                
                NpgsqlCommand cmd = new NpgsqlCommand(
                    String.Format("SELECT * FROM \"{0}\".\"{1}\" LIMIT 0", schemaName, tableName),
                    this.con);
                DataTable emptyTable = new DataTable();
                bool successful = true;
                try {
                    con.Open();
                    da.SelectCommand = cmd;
                    da.Fill(emptyTable);
                } catch {
                    successful = false;
                } finally {
                    con.Close();
                }
                if(successful) {
                    TableInfo tableInfo = new TableInfo(tableName);
                    switch(relkind) {
                        case "r": tableInfo.IsView = false; break;
                        case "v": tableInfo.IsView = true; break;
                        default: continue;
                    }
                    if(!DBI.ContainsSchema(schemaName)) {
                        DBI.Add(new SchemaInfo(schemaName));
                    }
                    DBI[schemaName].Add(tableInfo);
                    dataTables.Add(new Tuple<SchemaInfo, TableInfo>(DBI[schemaName], DBI[schemaName][tableName]), emptyTable);
                }
            }
            //this.con.Close();

            return dataTables;
        }
        


        public DataBaseInfo readDBINFO() { 
            //Get Schemas names & OIDs
            List<Int64> schemaOIDs;
            String[] schemaNames =  new string[] { "CORE", "C_GFZ", "CKEYS","public" };
            DataTable dt_pg_namespace = this.getNamespaces(schemaNames, out schemaOIDs);
            dt_pg_namespace.Columns.Add("SCHEMA", typeof(String));
            foreach(DataRow r in dt_pg_namespace.Rows) r["SCHEMA"] = r["nspname"]; 
            

            //Get Table Names & OIDs
            List<Int64> classOIDs;
            DataTable dt_pg_class = this.getClasses(schemaOIDs, out classOIDs);
            dt_pg_class.Columns.Add("TABLE");
            dt_pg_class.Columns.Add("SCHEMA");
            foreach(DataRow row in dt_pg_class.Rows) {
                row["TABLE"] = row["relname"];
                var schema = (from r in dt_pg_namespace.AsEnumerable()  
                           where r.Field<long>("oid") == (long) row["relnamespace"] 
                           select r.Field<String>("SCHEMA")).First();
                row["SCHEMA"] = schema;
            }
            
            //Get Constraints
            DataTable dt_pg_constraint = this.getConstraints(schemaOIDs);
            dt_pg_constraint.Columns.Add("SCHEMA", typeof(String));
            dt_pg_constraint.Columns.Add("TABLE", typeof(String));
            
            foreach(DataRow row in dt_pg_constraint.Rows) {

                var ST = (from r in dt_pg_class.AsEnumerable()
                          where r.Field<long>("oid") == (long)row["conrelid"]
                          select new {
                              table=r.Field<String>("TABLE"),
                              schema=r.Field<String>("SCHEMA")
                          }).First();
                row["SCHEMA"] = ST.schema;
                row["TABLE"] = ST.table;
            }

            //Get column definitions
            DataTable dt_pg_attribute = getAttributes(classOIDs);
            dt_pg_attribute.Columns.Add("SCHEMA", typeof(String));
            dt_pg_attribute.Columns.Add("TABLE", typeof(String));
            dt_pg_attribute.Columns.Add("COLUMN", typeof(String));
            foreach(DataRow row in dt_pg_attribute.Rows) {
                long tableOID = (long)row["attrelid"];
                row["COLUMN"] = row["attname"];
                var ST = (from r in dt_pg_class.AsEnumerable()
                         where r.Field<long>("oid") == tableOID
                         select new {
                             schema=r.Field<String>("SCHEMA"),
                             table = r.Field<String>("TABLE")
                         }).First();
                row["SCHEMA"] = ST.schema;
                row["TABLE"] = ST.table;
            }


            //Get PostGIS and other column definitions
            Dictionary<Tuple<String, String, String>, ColumnInfo> specialColumns = new Dictionary<Tuple<String, String, String>, ColumnInfo>();
            foreach(DataRow row in this.getPostGISColumns(schemaNames).Rows){
                String schema = (string)row["f_table_schema"];
                String table = (string) row["f_table_name"];
                String column =  (string) row["f_geometry_column"];   
                GeoAPI.Geometries.OgcGeometryType gtype;
                switch ((string) row["type"]){
                    case "POINT": gtype = GeoAPI.Geometries.OgcGeometryType.Point; break;
                    case "POLYGON": gtype = GeoAPI.Geometries.OgcGeometryType.Polygon; break;
                    case "LINE": gtype = GeoAPI.Geometries.OgcGeometryType.LineString; break;
                    default: throw new NotImplementedException("Unsupported Geometry Type:"+(string) row["type"]);
                }
                ColumnInfoGeometry geometryColumn = new ColumnInfoGeometry(column , gtype, (int)row["srid"]);
                Tuple<String, String, String> key = new Tuple<string,string,string>(schema, table, column);
                specialColumns.Add(key, geometryColumn);
            }

            //Multicolumndescriptions
            DataTable multiRowDescriptions =  this.readXMLMultiColumnDescriptions();
            var xSpecDescr = from row in multiRowDescriptions.AsEnumerable() 
                    where row.Field<ColumnInfoFile.RelatedColumnTypes>("COLUMNTYPE") == ColumnInfoFile.RelatedColumnTypes.cfile &&
                          row.Field<String>("DESCRIPTIONTYPE") == "spectrumdescription" 
                    select row;
            var imgColumns = from row in multiRowDescriptions.AsEnumerable() 
                    where (row.Field<ColumnInfoFile.RelatedColumnTypes>("COLUMNTYPE") == ColumnInfoFile.RelatedColumnTypes.cfile ||
                          row.Field<ColumnInfoFile.RelatedColumnTypes>("COLUMNTYPE") == ColumnInfoFile.RelatedColumnTypes.cimg_preview) && 
                          row.Field<String>("DESCRIPTIONTYPE") == "imagedescription" 
                    select row;
            foreach(DataRow row in xSpecDescr) {
                String schema = (string)row["SCHEMA"];
                String table = (string) row["TABLE"];
                String column =  (string) row["COLUMN"]; 
                Tuple<String, String, String> key = new Tuple<string,string,string>(schema, table, column);
                ColumnInfoSpectralFile ci = new ColumnInfoSpectralFile(column);
                specialColumns.Add(key, ci);
            }
            foreach(DataRow row in imgColumns){
                String schema = (string)row["SCHEMA"];
                String table = (string) row["TABLE"];
                String column =  (string) row["COLUMN"]; 
                Tuple<String, String, String> key = new Tuple<string,string,string>(schema, table, column);
                ColumnInfoImageFile ci = new ColumnInfoImageFile(column);
                specialColumns.Add(key, ci);
            }
            
            //Normal columns with specific contraints



            
            //Now create all TableInfos
            DataBaseInfo DBINFO = new DataBaseInfo("SPECLIB");
            Dictionary<Tuple<SchemaInfo, TableInfo>, DataTable> emptyTables = this.getEmptyTables3(dt_pg_class, ref DBINFO);
            
            //Read multi column descriptions
            //stored as: Dictionary<String{Schema.Table}, Hash{name, columnType}>
            //Dictionary<String, Hashtable> multicolumndescriptions = this.readXMLMultiColumnDescriptions();

            //Read single column constraints that are not stored in the system catalog (or hard to retrieve from)
            foreach(Tuple<SchemaInfo, TableInfo> tupel in emptyTables.Keys) {
               SchemaInfo schemaInfo = tupel.Item1;
               TableInfo  tableInfo  = tupel.Item2;

                DataTable dt = emptyTables[tupel];
                if(dt == null) continue;
                foreach(DataColumn dataColumn in dt.Columns) {
                    String column = dataColumn.ColumnName;
                    Tuple<String, String, String> key = new Tuple<string, string, string>(
                        schemaInfo.Name, tableInfo.Name, column);
                    ColumnInfo ci = null;

                    //Is it a byte-array column? 
                    if(specialColumns.Keys.Contains(key)) {
                        ci = specialColumns[key]; //Special colum (Image -or Spectrum File)
                    } else if(dataColumn.DataType == typeof(Byte[])) {
                        ci = new ColumnInfoFile(column); //Normal file
                    } else {
                        //Add normal tableName column
                        ci = new ColumnInfo(column, dataColumn.AutoIncrement, dataColumn.DataType);

                    }

                    //Column Settings
                    var pg_class_row = (from r in dt_pg_class.AsEnumerable()
                                        where r.Field<String>("TABLE") == tableInfo.Name &&
                                            r.Field<String>("SCHEMA") == schemaInfo.Name
                                        select r).First();
                    tableInfo.Add(ci);
                }
            }

            //Add intertable-relationsships


            //Stuff.DataHelper.print(dt_pg_constraint);

            //Add constraints
            foreach(DataRow row in dt_pg_constraint.Rows){
                String contype = (String)  row["contype"];
                Int16[] conkey = (Int16[]) row["conkey"]; //If a tableName constraint (including foreign keys, 
                //but not constraint triggers), list of the constrained columns
                long conrelid = (long)row["conrelid"]; //the tableName the constraint is on
                                
                String schema = (String) row["SCHEMA"];
                String table  = (String) row["TABLE"];
                if(!DBINFO.ContainsTable(schema, table)) continue;
                TableInfo ti = DBINFO[schema][table];

                var x = from r in dt_pg_attribute.AsEnumerable()
                        where r.Field<String>("SCHEMA") == schema &&
                              r.Field<String>("TABLE")  == table 
                        select new {
                            attrelid = r.Field<long>("attrelid"),
                            attname  = r.Field<String>("attname"),
                            attnum   = r.Field<Int16>("attnum"),
                            attndims = r.Field<Int32>("attndims")
                        };
                if(x.Count() == 0) continue;
                String[] columns = (from x2 in x
                                    where conkey.Contains(x2.attnum)
                                    select x2.attname).ToArray();
                if(columns.Count() == 0) continue;
                try {
                    switch(contype) {
                        case "p":

                            ti.addKey(columns, true);

                            break;
                        case "c":

                            break;
                        case "f":
                            long confrelid = (long)row["confrelid"]; //long confrelid = (long) result["confrelid"]
                            Int16[] confkey = (Int16[])row["confkey"]; //If a foreign key, list of the referenced columns

                            var FSrc = (from r in dt_pg_class.AsEnumerable()
                                        where r.Field<long>("oid") == confrelid
                                        select new {
                                            FSchema = r.Field<String>("SCHEMA"),
                                            FTable  = r.Field<String>("TABLE")
                                        }).First();
                            if(!DBINFO.ContainsTable(FSrc.FSchema, FSrc.FTable)) {
                                continue;
                            }
                            String[] fcolumns = (from r in dt_pg_attribute.AsEnumerable()
                                                 where r.Field<long>("attrelid") == confrelid &&
                                                  confkey.Contains(r.Field<Int16>("attnum"))
                                                 select r.Field<String>("attname")).ToArray();
                            //Console.WriteLine(String.Format("{0} {1} from {2} {3} ",schema, table, FSrc.FSchema, FSrc.FTable));
                            TableInfo fti = DBINFO[FSrc.FSchema][FSrc.FTable];
                            ForeignKey fkey = new ForeignKey(DBINFO[FSrc.FSchema][FSrc.FTable].getColumns(fcolumns), ti.getColumns(columns));
                            ti.addForeignKey(fkey);
                            fti.addForeignKey(fkey);
                            break;
                        case "u": //Secondary key / unique
                            ti.addKey(columns, false);
                            break;
                        default: //nothing
                            break;

                    }
                } catch{
                    //nothing
                }
            }
            //Add not null constraint
            var notNullColumns = from r in dt_pg_attribute.AsEnumerable()
                                 let schema = r.Field<String>("SCHEMA")
                                 let table  = r.Field<String>("TABLE")
                                 let column  = r.Field<String>("COLUMN")
                                 where r.Field<bool>("attnotnull") == true &&
                                    DBINFO.ContainsColumn(schema, table, column)
                                 select DBINFO[schema][table][column];
                                 

            foreach(var nnC in notNullColumns) {
                nnC.AllowDBNull = false;
            }

            //Add multi-column descriptions
            foreach(Tuple<String, String, String> key in specialColumns.Keys) {
                String schema = key.Item1;
                String table  = key.Item2;
                String column = key.Item3;
                try {
                    var Items = from descr in multiRowDescriptions.AsEnumerable()
                                where descr.Field<String>("SCHEMA") == schema &&
                                          descr.Field<String>("TABLE") == table &&
                                          descr.Field<String>("COLUMN") == column
                                select new {
                                     rcolumnnames = descr.Field<List<String>>("RCOLUMNNAMES"),
                                     rcolumntypes = descr.Field<List<ColumnInfoFile.RelatedColumnTypes>>("RCOLUMNTYPES")
                                };
                    if(Items.Count() > 0) {
                        ColumnInfoImageFile cImage = DBINFO[schema][table][column] as ColumnInfoImageFile;
                        if(cImage != null) {
                            var FirstItem = Items.First();
                            for(int i = 0; i < FirstItem.rcolumnnames.Count; i++) {
                                cImage.setRelatedColumn(FirstItem.rcolumnnames[i], FirstItem.rcolumntypes[i]);

                            }
                        }

                        ColumnInfoFile c = DBINFO[schema][table][column] as ColumnInfoFile;
                        if(c != null) {
                            var FirstItem = Items.First();

                            for(int i = 0; i < FirstItem.rcolumnnames.Count; i++) {
                                c.setRelatedColumn(FirstItem.rcolumnnames[i], FirstItem.rcolumntypes[i]);

                            }
                        }

                       
                    
                    }

                    
                } catch {
                    //String s = "";
                }
            }
            
            //Add value restrictions
            Dictionary<Tuple<String, String, String>, 
                List<DataBaseDescription.ColumnInfo.ConstrainedValue>> constraints = this.readXMLValueContraints();
            foreach(Tuple<String, String, String> t in constraints.Keys) {
                DBINFO[t.Item1][t.Item2][t.Item3].possibleValues = constraints[t];
            
            }

            return DBINFO;
        }

      

        #region XML Stuff

        private Dictionary<Tuple<String, String, String>, 
                List<DataBaseDescription.ColumnInfo.ConstrainedValue>> readXMLValueContraints() {
                    Dictionary<Tuple<String, String, String>,
                        List<DataBaseDescription.ColumnInfo.ConstrainedValue>> constraints =
            new Dictionary<Tuple<string,string,string>,List<ColumnInfo.ConstrainedValue>>();
            
            XPathNavigator NAV = this.xmlDoc.CreateNavigator();
            foreach(XPathNavigator N in NAV.Select("root/valueconstraints/child::*")) {
                String schema = N.GetAttribute("schema", "");
                String table = N.GetAttribute("table", "");
                String column = N.GetAttribute("column","");

                List<ColumnInfo.ConstrainedValue> constraintsList = new List<ColumnInfo.ConstrainedValue>();
 
                foreach(XPathNavigator Nc in N.Select("constraint")) {
                    String value = readXML_getXPathNodeValue(Nc,"value");
                    String tooltip = readXML_getXPathNodeValue(Nc,"tooltip");
                    constraintsList.Add(new ColumnInfo.ConstrainedValue(value, tooltip));
                }

                constraints.Add(new Tuple<string, string, string>(schema, table, column),
                                constraintsList);
            }
            return constraints;
        }

        private DataTable readXMLMultiColumnDescriptions() {
            DataTable columnDescriptions = new DataTable();
            columnDescriptions.Columns.Add("SCHEMA", typeof(String));
            columnDescriptions.Columns.Add("TABLE", typeof(String));
            columnDescriptions.Columns.Add("DESCRIPTIONTYPE", typeof(String));
            columnDescriptions.Columns.Add("COLUMN", typeof(String));
            columnDescriptions.Columns.Add("COLUMNTYPE", typeof(ColumnInfoFile.RelatedColumnTypes));
            columnDescriptions.Columns.Add("RCOLUMNNAMES", typeof(List<String>));
            columnDescriptions.Columns.Add("RCOLUMNTYPES", typeof(List<ColumnInfoFile.RelatedColumnTypes>));
        
//            
//            columnDescriptions.Columns.Add("DESCRIPTIONS", typeof(Dictionary<ColumnInfoFile.RelatedColumnTypes, String>));

            Type     AllRCTypes = typeof(ColumnInfoFile.RelatedColumnTypes);
            String[] AllRCTypeNames = Enum.GetNames(AllRCTypes);

            XPathNavigator NAV = this.xmlDoc.CreateNavigator();
            foreach(XPathNavigator N in NAV.Select("root/multicolumndescriptions/child::*")) {
                String schema = N.GetAttribute("schema", "");
                String table = N.GetAttribute("table", "");
                String descriptionType = N.Name;

                
                List<String> columnNames = new List<string>();
                List<ColumnInfoFile.RelatedColumnTypes> columnTypes = new List<ColumnInfoFile.RelatedColumnTypes>();

                foreach(String typeName in AllRCTypeNames) {
                    String RCName = this.readXML_getXPathNodeValue(N, typeName);
                    if(RCName != null) {
                        ColumnInfoFile.RelatedColumnTypes RCType = 
                        (ColumnInfoFile.RelatedColumnTypes)Enum.Parse(AllRCTypes, typeName);

                        columnNames.Add(RCName);
                        columnTypes.Add(RCType);

                        columnDescriptions.Rows.Add(new Object[]{schema, table,descriptionType,
                            RCName, RCType, columnNames, columnTypes});
                    }
                }
               
            }
            return columnDescriptions;
        }

        private String readXML_getXPathNodeValue(XPathNavigator nav, String pathToNode) {
            XPathNavigator n = nav.SelectSingleNode(pathToNode);
            if(n == null) return null;
            return n.Value;
        }

        private String readXML_getXPathNodeAttribute(XPathNavigator nav, String pathToNode, String attribute) {
            XPathNavigator n = nav.SelectSingleNode(pathToNode);
            if(n == null) return null;
            return n.GetAttribute(attribute, "");
        }

        #endregion

        private String ST(String schema, String table) {
            return this.ST(schema, table, false);
        
        }
        /// <summary>
        /// Just creates as schemaName tableName string, e.g. "CORE.xyz"
        /// </summary>
        /// <param name="schemaName"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        private String ST(String schema, String table, bool oneString) {
            if(oneString) {
                return String.Format("\"{0}.{1}\"", schema, table);
            }
            return String.Format("\"{0}\".\"{1}\"", schema, table);
        }

        private DataTable getTable(String tableName, Condition condition) {
            return this.getTable(tableName, new ConditionList(condition, ConditionList.Op.AND));
        }
        private DataTable getTable(String[] toSelect, String tableName, Condition condition) { 
            return this.getTable(toSelect, tableName, new ConditionList(condition, ConditionList.Op.AND));
        }
        private DataTable getTable(String tableName, ConditionList conditionList) {
            return this.getTable(new String[]{"*"}, tableName, conditionList);
        }
        private DataTable getTable(String[] toSelect, String tableName, ConditionList conditionList) { 
            NpgsqlCommand cmd = new NpgsqlCommand();
            cmd.Connection = this.con;
            cmd.CommandText = String.Format("SELECT {0} FROM {1}", TextHelper.combine(toSelect, ","),  tableName);
            if(conditionList != null && !conditionList.IsEmpty) {
                cmd.CommandText += " WHERE ";
                SQLCommandBuilder.CommandBuilder.appendCmd(ref cmd, conditionList.getCmd());
            }
            return this.getData(cmd, tableName);
        }

        private DataTable getData(NpgsqlCommand cmd, String tableName) {
            String s = CommandBuilder.CommandToString(cmd);
            if(cmd.Connection == null) cmd.Connection = this.con;
            NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);
            DataTable dt = new DataTable(tableName);
            try {
                cmd.Connection.Open();
                //da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                //da.Fill(dataSet, tableName);
                da.Fill(dt);
            } catch {
                //String stop = "";
            } finally {
                cmd.Connection.Close();
            }
            return dt;
        }

        private DataTable getEmptyTable(String schemaTableName) {
            //empty tableName does not exist - wrong initialised?
            
            DataTable dt = new DataTable(schemaTableName);
            NpgsqlCommand cmd = new NpgsqlCommand(String.Format("SELECT * FROM {0} LIMIT 0", schemaTableName), con);
            try {
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd);

                //da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
                //da.Fill(dataSet, tableName);
                da.Fill(dt);
            } catch(Exception ex) {
                throw ex;
            } finally {
                cmd.Connection.Close();
            }
            return dt;
        }
    }
}
